package com.javachmapi.jdbctemplate.dao.impl;

import com.javachmapi.jdbctemplate.dao.UserDao;
import com.javachmapi.jdbctemplate.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 持久层实现类
 * @Description： create by 宋利军
 */
@Service
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 通过ID查询单条数据
     *  1、单条数据结果集的处理(RowCallbackHandler)
     * @param id 主键
     * @return 实例对象
     */
    @Override
    public User queryById(Integer id) {
        String sql = "select * from user where id = ?";
        final User user = new User();
        jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
            }
        });
        return user;
    }

    /**
     * 查询指定行数据
     * 2、多条数据结果集的处理(RowCallbackHandler)
     * @param offset 查询起始位置
     * @param limit  查询条数
     * @return 对象列表
     */
    @Override
    public List<User> queryAllByLimit(int offset, int limit) {
        String sql = "select * from user where id between ? and ?";
        final List<User> users = new ArrayList<User>();
        jdbcTemplate.query(sql, new Object[] { offset,limit }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
        });
        return users;
    }

    /**
     * 2、多条数据结果集的处理(RowCallbackHandler)
     * @return 对象列表
     */
    @Override
    public List<User> queryAll() {
        String sql = "select * from user";
        final List<User> users = new ArrayList<User>();
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                users.add(user);
            }
        });
        return users;
    }

    /**
     *
     * @param user 实例对象
     * @return 影响行数
     */
    @Override
    public int insert(User user) {
        final String sql = "insert into user (username,password) values(?,?)";
        //return jdbcTemplate.update(sql,user);
        return this.jdbcTemplate.update(sql,new Object[]{user.getUsername(),user.getPassword()});
    }

    /**
     *
     * @param users 实例对象集
     * @return 影响行数
     */
    @Override
    public void insert(final List<User> users) {
        final String sql = "insert into user (username,password) values(?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement ps, int index) throws SQLException {
                User user = users.get(index);
                ps.setString(1,user.getUsername());
                ps.setString(2,user.getPassword());
            }
            public int getBatchSize() {
                return users.size();
            }
        });
    }

    /**
     * 修改数据
     *
     * @param user 实例对象
     * @return 影响行数
     */
    @Override
    public int update(User user) {
        final String sql = "update user set username=?,password=? where id=?";
        //return jdbcTemplate.update(sql,user);
        return this.jdbcTemplate.update(sql,new Object[]{user.getUsername(),user.getPassword(), String.valueOf( user.getId() )});
    }

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    @Override
    public int deleteById(Integer id) {
        final String sql = "delete from user where id = ?";
        return this.jdbcTemplate.update(sql,new Object[]{id});
    }
}
